########################################################################
#  Pyfm - personal finance data-base
#  Copyright (C) 2007,8 Ehud Ben-Reuven
#  udi@benreuven.com
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation version 2.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
########################################################################
"""Convert Direct-Net checking transactions CSV file to flat lines.
"""
from csv2flt import *
from lib import dirs
from obj.flat import stagvalue,ptagvalue,pacctid,psecid
import re
import logging

def acctidre(name):
	return '(?P<%s>[\d\-]+)'%name

htf_list=[
# version 0
([
	['Exported from Direct Net on ',eurdtzre('DTASOF'),'','','',''],
	['Booking entries','','','','',''],
	['Account number',acctidre('ACCTID'),'','','',''],
	['Balance',currencyre('CURRENCY')+' '+commanumre('UNITS'),'','','',''],
	['Holder',strre('OWNER'),'','','',''],
	['Bank',strre('ORG'),'','','',''],
	['Description',strre('ACCTMEMO'),'','','',''],
	['Current date',eurdtzre('DTASOF'),'','','',''],
	['Booking entries from '+eurdre('DTSTART')+' - '+eurdre('DTEND')+' \(including provisional bookings\)','','','','',''],
	['Booking date','Text','Debit','Credit','Value date','Balance'],
],
[eurdre('DTSETTLE'),
      strre('MEMO'),
      pvaluere('UNITS_debit')+'|', # The | is to allow also blank cells
      pvaluere('UNITS_credit')+'|',
      eurdre('DTAVAIL'),
      valuere('TOTAL')+'|'],
[['Total of column','',valuere('UNITS_total_debit'),valuere('UNITS_total_credit'),'','']]
),
# version 1 introduced somewhere 070501 and 070626
([
	['Exported from Direct Net on',eurdtzre('DTASOF'),'','','',''],
	['Booking Entries','','','','',''],
	['Account Number',acctidre('ACCTID'),'','','',''],
	['Balance',currencyre('CURRENCY')+' '+commanumre('UNITS'),'','','',''],
	['Holder',strre('OWNER'),'','','',''],
	['Bank',strre('ORG'),'','','',''],
	['Description',strre('ACCTMEMO'),'','','',''],
	['Current Date ',eurdtzre('DTASOF'),'','','',''],
	['Booking Entries from '+eurdre('DTSTART')+' - '+eurdre('DTEND')+' \(Including Provisional Bookings\)','','','','',''],
	['Booking Date','Text','Debit','Credit','Value Date','Balance'],
],
[eurdre('DTSETTLE'),
      strre('MEMO'),
      pvaluere('UNITS_debit')+'|', # The | is to allow also blank cells
      pvaluere('UNITS_credit')+'|',
      eurdre('DTAVAIL'),
      valuere('TOTAL')+'|'],
[['Total of Column','',valuere('UNITS_total_debit'),valuere('UNITS_total_credit'),'','']]
),
# version 2 introduced before 070910
([
	['Exported from Direct Net on',eurdtzre('DTASOF'),'','','',''],
	['Booking Entries','','','','',''],
	['Account Number',acctidre('ACCTID'),'','','',''],
	['Balance',currencyre('CURRENCY')+' '+commanumreint('UNITS'),'','','',''],
	['IBAN',strre('ACCTID'),'','','',''],
	['Bank',strre('ORG'),'','','',''],
	['Holder',strre('OWNER'),'','','',''],
	['Description',strre('ACCTMEMO'),'','','',''],
	['Booking Entries from '+eurdre('DTSTART')+' - '+eurdre('DTEND')+' \(Including Provisional Bookings\)','','','','',''],
	['Booking Date','Text','Debit','Credit','Value Date','Balance'],
],
[eurdre('DTSETTLE'),
      strre('MEMO'),
      pvaluereint('UNITS_debit')+'|', # The | is to allow also blank cells
      pvaluereint('UNITS_credit')+'|',
      eurdre('DTAVAIL'),
      valuereint('TOTAL')+'|'],
[['Total of Column','',valuereint('UNITS_total_debit'),valuereint('UNITS_total_credit'),'','']]
),
]

pre=None

def flat(fname,fout,linetxt):
	h,t,f,version=readcsvtable_multiformat(fname,htf_list)
	hl=0

	l=h[hl]
	hl+=1
	dtasof = l['DTASOF_year']+l['DTASOF_month']+l['DTASOF_day']+\
		      l['DTASOF_hours']+l['DTASOF_min']+l['DTASOF_TZ']
	hl+=1

	acctid=h[hl]['ACCTID']
	hl+=1

	currency=h[hl]['CURRENCY']
	curdef=currency
	accttype='CHECKING'
	secid='CURRENT='+accttype+'='+currency
	units=h[hl]['UNITS'].replace(',','')
	hl+=1
	
	if version>=2:
		acctid1='IBEN='+h[hl]['ACCTID']
		hl+=1

	if version<2:
		owner=h[hl]['OWNER']
		hl+=1
		org=h[hl]['ORG']
		hl+=1
	else:
		org=h[hl]['ORG']
		hl+=1
		owner=h[hl]['OWNER']
		hl+=1

	acctmemo=h[hl]['ACCTMEMO']
	hl+=1

	if version<2:
		l=h[hl]
		hl+=1
		dtasof1 = l['DTASOF_year']+l['DTASOF_month']+l['DTASOF_day']+\
			   l['DTASOF_hours']+l['DTASOF_min']+l['DTASOF_TZ']
		if dtasof != dtasof1:
			print 'Warning Export date and Current date dont match'

	l=h[hl]
	hl+=1
	dtstart = l['DTSTART_year']+l['DTSTART_month']+l['DTSTART_day']
	dtend = l['DTEND_year']+l['DTEND_month']+l['DTEND_day']
	
	print >>fout,stagvalue('ACCTID',acctid),stagvalue('CURDEF',curdef),stagvalue('OWNER',owner),
	print >>fout,stagvalue('ORG',org),stagvalue('ACCTTYPE',accttype),stagvalue('ACCTMEMO',acctmemo),
	if version>=2:
		print >>fout,stagvalue('ACCTID',acctid1),
	print >>fout,linetxt
	
	print >>fout,stagvalue('ACCTID',acctid),
	print >>fout,stagvalue('FULL','TRUE'),
	print >>fout,stagvalue('DTSTART',dtstart),stagvalue('DTEND',dtend),
	print >>fout,linetxt
	
	holding=stagvalue('ACCTID',acctid)+' '+stagvalue('SECID',secid)
	print >>fout,holding,stagvalue('DTASOF',dtasof),stagvalue('UNITS',units),
	print >>fout,linetxt
	
	print >>fout,stagvalue('SECID',secid),stagvalue('ORIGCURRENCY',currency), # CURDEF is good for the Account but the Asset by itself does not know inwhich Account it is and therefore can not use ORIGCURRENCY
	print >>fout,linetxt
	
	total_debit=0
	total_credit=0
	previouse_dtsettle=None
	for l in t:
		dtsettle= l['DTSETTLE_year']+l['DTSETTLE_month']+l['DTSETTLE_day']
		assert len(dtsettle)==8
		# There can be multiple transactions on the same day
		# (each will have a different NLINE so it will be taken to be a different transaction)
		# The newest appear first.
		assert previouse_dtsettle==None or previouse_dtsettle>=dtsettle
		previouse_dtsettle=dtsettle

		# TODO: what is dtvalue (it is not DTSETTLE or DTTRADE, I checkecked information on individual transactions.)
		dtvalue= l['DTAVAIL_year']+l['DTAVAIL_month']+l['DTAVAIL_day']
		assert len(dtvalue)==8
		
		memo=l['MEMO']
		
		debit=l['UNITS_debit']
		credit=l['UNITS_credit']
		if debit and credit: raise Exception(str(reader.line_num))
		if debit:
			debit=float(debit)
			total_debit+=debit
			units=-debit
			trntype='DEBIT'
		elif credit:
			credit=float(credit)
			total_credit+=credit
			units=credit
			trntype='CREDIT'
		else: raise Exception(str(reader.line_num))

		print >>fout,holding,stagvalue('DTSETTLE',dtsettle),
		print >>fout,stagvalue('TRNTYPE',trntype),stagvalue('UNITS',units),
		memo1=memo[:45].strip()
		memo2=memo[45:].strip()
		print >>fout,stagvalue('MEMO',memo1),
		if memo2:
			print >>fout,stagvalue('MEMO',memo2),
		try:
			t_units=re.match( '(?P<U>\d{1,3}(,\d{3})*) [A-Z0-9]',memo2).group('U')  # using match force the number to appear at the start
			secname=memo2[len(t_units)+1:].strip()
			t_units=float(t_units.replace(',',''))
			if not secname: raise Exception('miissing SECNAME')
		except:
			t_units=None
			secname=None
		if memo1 in ['Securities purchase', 'Deposit', 'Reversal Redemption']:
			print >>fout,stagvalue('XSECNAME',secname),stagvalue('NAME',secname),
			print >>fout,stagvalue('XTRNTYPE','BUY'),
			if t_units:
				print >>fout,stagvalue('XUNITS',t_units),
			else:
				logging.warning('%s','Units missing')
		elif memo1 in ['Securities sale','Redemption','Redemption of fund units','Capital repayment','Custody account withdrawal']:
			if not t_units: raise Exception('Missing units')
			print >>fout,stagvalue('XSECNAME',secname),stagvalue('NAME',secname),
			print >>fout,stagvalue('XTRNTYPE','SELL'),stagvalue('XUNITS',-t_units),
		elif memo1=='Cash dividend':
			if not t_units: raise Exception('Missing units')
			print >>fout,stagvalue('XSECNAME',secname),stagvalue('NAME',secname),
			print >>fout,stagvalue('XTRNTYPE','DIV'),stagvalue('INTUNITS',t_units),
		elif memo1=='Capital gain':
			if not t_units: raise Exception('Miissing units')
			print >>fout,stagvalue('XSECNAME',secname),stagvalue('NAME',secname),
			print >>fout,stagvalue('XTRNTYPE','CGAIN'),stagvalue('INTUNITS',t_units),
		elif memo1=='Interest payment' or memo1=='Equalisation payment':
			if not t_units: raise Exception('Missing units')
			print >>fout,stagvalue('XSECNAME',secname),stagvalue('NAME',secname),
			print >>fout,stagvalue('XTRNTYPE','INT'),stagvalue('INTUNITS',t_units),
		elif memo1=='Fid. call deposit int. settlemt.':
			print >>fout,stagvalue('XTRNTYPE','INT'),stagvalue('XACCTID',memo2),
		elif memo1=='Fiduciary call deposit' or memo1=='Fiduciary call deposit -  increase':
			print >>fout,stagvalue('XTRNTYPE','CREDIT'),stagvalue('XACCTID',memo2),
		elif memo1 in ['Fiduciary call dep. -  liquidation']:
			print >>fout,stagvalue('XTRNTYPE','DEBIT'),stagvalue('XACCTID',memo2),
		elif memo1=='Payment order' and memo2.startswith('MANAGEMENT FEE'):
			pass
		elif memo1 in ['Portfolio management fee','Reversal Portfolio management fee','Administration fees','Reversal Administration fees',
			       'Charges for tax documentation' ,'Balance of closing entries','Lending commission','Reversal Charges for tax documentati']:
			pass
		elif memo1 in ['Transfer', 'Payment order']:
			o=re.match('(?P<DIR>IN|EX) '+acctidre('ACCTID')+' AT (?P<XRATE>\d+\.\d+)',memo2)
			if o:
				if o.group('DIR')=='IN' and units>0: raise Exception('Wrong direction')
				print >>fout,stagvalue('XACCTID',o.group('ACCTID')),stagvalue('XRATE',o.group('XRATE')),
			else:
				logging.warning('%s','missing direction and account')
		else:
			print 'Unknown transaction',memo1
		print >>fout,linetxt
		
	if abs(float(f[0]['UNITS_total_credit'])-total_credit)>1 or abs(float(f[0]['UNITS_total_debit'])-total_debit)>1:
		print 'Warning total dont match',f[0]['UNITS_total_credit'],total_credit,f[0]['UNITS_total_debit'],total_debit

help="""Convert Direct-Net checking transactions CSV file to flat lines.
The CSV files should be located at $PYFM_DATA/imports/directnet/checking/
"""

def path(line):
	return ['directnet','checking']

ext='*.csv'
